import sqlite3
import pandas as pd

def query_data():
    """查询数据库中的数据"""
    conn = sqlite3.connect('ximalaya_data.db')
    
    # 查询专辑统计
    album_stats = pd.read_sql_query('''
        SELECT 
            album_category as 分类,
            COUNT(*) as 专辑数量,
            AVG(album_play_count) as 平均播放量,
            SUM(album_track_count) as 总音频数
        FROM albums 
        GROUP BY album_category 
        ORDER BY COUNT(*) DESC
    ''', conn)
    
    print("=== 专辑统计 ===")
    print(album_stats)
    
    # 查询热门专辑
    popular_albums = pd.read_sql_query('''
        SELECT 
            album_title as 专辑标题,
            album_author as 作者,
            album_play_count as 播放量,
            album_track_count as 音频数
        FROM albums 
        ORDER BY album_play_count DESC 
        LIMIT 10
    ''', conn)
    
    print("\n=== 热门专辑TOP10 ===")
    print(popular_albums)
    
    # 查询音频统计
    track_stats = pd.read_sql_query('''
        SELECT 
            COUNT(*) as 总音频数,
            AVG(track_play_count) as 平均播放量,
            MAX(track_play_count) as 最高播放量
        FROM tracks
    ''', conn)
    
    print("\n=== 音频统计 ===")
    print(track_stats)
    
    conn.close()

if __name__ == "__main__":
    query_data()